import pymysql
from flask import Flask, render_template, request, redirect, url_for

# 创建 flask 实例
app = Flask(__name__)

# 连接数据库
db_connect = pymysql.Connect(
    host="127.0.0.1",  
    port=3306,         # 数据库端口
    user="root",       # 数据库用户名
    password="123",    # 数据库密码
    database="ck_test",  # 数据库名称
    charset="utf8"     # 字符编码
)

@app.route("/")
def index():
    cursor = db_connect.cursor()
    sql_str = "select * from student where is_deleted = 1;"
    cursor.execute(sql_str)
    datas = cursor.fetchall()
    stu_list = [{"sid": s[0], "name": s[1], "age": s[2], "gender": s[3]} for s in datas]
    cursor.close()
    return render_template("index.html", students=stu_list)

@app.route("/add", methods=["GET", "POST"])
def add():
    if request.method == "GET":
        return render_template("add.html")
    elif request.method == "POST":
        stu_info = request.form
        name = stu_info.get("name")
        age = stu_info.get("age")
        gender = stu_info.get("gender")
        is_deleted = 1
        cursor = db_connect.cursor()
        sql_str = "INSERT INTO student (name, age, gender,is-deleted) VALUES(%s, %s, %s, %s)"
        cursor.execute(sql_str, [name, age, gender, is_deleted])
        db_connect.commit()
        cursor.close()
        return redirect(url_for('index'))
    else:
        return "请求方式错误"


@app.route("/change/<sid>", methods=["GET", "POST"])
def change(sid):
    if request.method == "GET":
        cursor = db_connect.cursor()
        sql_str = f"select * from student where sid = {sid};"
        cursor.execute(sql_str)
        stu = cursor.fetchone()
        print(stu)
        student_data = {
            "sid": stu[0],
            "name": stu[1],
            "age": stu[2],
            "gender": stu[3]
        }
        cursor.close()
        return render_template("change.html", student=student_data)
    elif request.method == "POST":
        stu_info = request.form
        name = stu_info.get("name")
        age = stu_info.get("age")
        gender = stu_info.get("gender")
        cursor = db_connect.cursor()
        sql_str = "update student set name=%s, age=%s, gender=%s where sid=%s;"
        cursor.execute(sql_str, [name, age, gender, sid])
        db_connect.commit()
        cursor.close()
        return redirect("/")
    else:
        return "请求方法错误"

@app.route("/delete/<sid>")
def delete(sid):
    cursor = db_connect.cursor()
    sql_str = f"update student set is-deleted = 0 where sid={sid};"
    cursor.execute(sql_str)
    db_connect.commit()
    cursor.close()
    return redirect(url_for("index"))


if __name__ == '__main__':
    app.run(debug=True, port=5055)